﻿#region  文件信息

// ---------------------------------------------------------------------------------------
// <copyright file="Excel.cs" company="">
// 作者：李帅伦
// </copyright>
// <summary>
// 类型简介：
// 类型详细：Excel.cs
// 创建时间：2015-03-23 16:10
// 修改时间：2015-03-23 16:09
// </summary>
// ---------------------------------------------------------------------------------------
#endregion

using System.Runtime.InteropServices;

namespace WebCommon
{
    #region

    using System;
    using System.Data;
    using System.Data.OleDb;

    #endregion

    /// <summary>
    /// The excel helper.
    /// </summary>
    public class ExcelHelper
    {


        #region Public Methods and Operators

        

        /// <summary>
        /// The get excel content.
        /// </summary>
        /// <param name="filePath">
        /// The file path.
        /// </param>
        /// <param name="sheetName">
        /// The sheet name.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable"/>.
        /// </returns>
        public static DataTable GetExcelContent(string filePath, string sheetName)
        {
            if (sheetName == "_xlnm#_FilterDatabase")
            {
                return null;
            }

            DataSet dateSet = new DataSet();
            string connectionString =
                string.Format(
                    "Provider=M0icrosoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=NO;IMEX=1;'", 
                    filePath);
            string commandString = string.Format("SELECT * FROM [{0}$]", sheetName);
            using (var connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                using (var command = new OleDbCommand(commandString, connection))
                { 
                    var myData = new OleDbDataAdapter(commandString, connection);
                    myData.Fill(dateSet, sheetName);
                    DataTable table = dateSet.Tables[sheetName];
                     
                    return table;
                }
            }
        }

        #endregion
    }
}